package com.etc.team2.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.etc.team2.bean.Station;
import com.etc.team2.jdbc.JDBCHelper;

public class StationDAO {
	private ResultSet set;
	private JDBCHelper helper;

	public StationDAO() {
		helper = new JDBCHelper();
	}
	
	public List<Station> AllStation() {// 获取所有站台信息，用于管理员管理所有站台
		List<Station> list = new ArrayList<Station>();
		String sql = "select * from bus_station_info";

		set = helper.executeQueryByPrepareStatement(sql, null);
		try {
			while (set.next()) {
				Station sa = new Station();
				List<String> linelist = new ArrayList<String>();
				int id = set.getInt(1);
				String name = set.getString(2);
				String address = set.getString(3);
				sa.setStationId(id);
				sa.setStationName(name);
				sa.setAddress(address);
				sql = "select a.lineName from bus_line_info a,bus_lineandstation_info b where "
						+ "a.lineId=b.lineId and b.stationId=" + id;
				ResultSet set2 = helper.executeQueryByPrepareStatement(sql,
						null);
				while (set2.next()) {
					linelist.add(set2.getString(1));
				}
				sa.setLinelist(linelist);
				list.add(sa);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return list;
	}
	
public Station getStation(String stationName) {// 根据站台名获取站台信息，运用在用户搜索指定站台信息时。
		
		String sql = "select stationId,stationName,address from bus_station_info where stationName="
			+"\'"+ stationName+"\'";

		set = helper.executeQueryByPrepareStatement(sql, null);
		try {
			while (set.next()) {
				Station sa = new Station();
				List<String> linelist = new ArrayList<String>();
				int id = set.getInt(1);
				String name = set.getString(2);
				String address = set.getString(3);
				sa.setStationId(id);
				sa.setStationName(name);
				sa.setAddress(address);
				sql = "select a.lineName from bus_line_info a,bus_lineandstation_info b where "
						+ "a.lineId=b.lineId and b.stationId=" + id;
				ResultSet set2 = helper.executeQueryByPrepareStatement(sql,
						null);
				while (set2.next()) {
					linelist.add(set2.getString(1));
				}
				sa.setLinelist(linelist);
				return sa;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return null;
	}
    
	public boolean addStation(Station station)
	{/*
	*根据接收到的Station对象，对bus_station_info表进行添加
	*返回boolean类型
	**/
		Object[] objects=new Object[]{station.getStationName(), station.getAddress()};
		String sql="insert into bus_station_info(stationName,address) values(?,?) ";
		int i=helper.executeUpdateByPrepareStatement(sql, objects);
		if (i!=0) {
			return true;
		}
		return false;
	}
	public boolean deleteStation(int stationId)
	{/*
	*根据接收到的stationId，对bus_station_info表进行删除操作
	*注意修改和stationId在同一路线的其他站点的上行和下行
	*注意，需先删除bus_lineandstation_info表中的记录
	*
	*/
		String sql1="select b.goup,b.lineId,b.godown from bus_lineandstation_info b where stationId="+stationId;
		ResultSet set1= helper.executeQueryByPrepareStatement(sql1, null);
		try {
			while (set1.next()) {
				String sql2="select a.goup from bus_lineandstation_info a where a.lineId="+set1.getInt(2)+" and a.goup>"+set1.getInt(1);
				String sql3="select a.godown from bus_lineandstation_info a where a.lineId="+set1.getInt(2)+" and a.godown>"+set1.getInt(3);
				ResultSet set2=	helper.executeQueryByPrepareStatement(sql2, null);
				ResultSet set3=	helper.executeQueryByPrepareStatement(sql3, null);
			    int count1=0;
			    int count2=0;
			    int i=0;
			    int j=0;
			    while (set2.next()) {
					String sql4="update bus_lineandstation_info set goup="+set2.getInt(1)+"-1";
					count1++;
					i=helper.executeUpdateByPrepareStatement(sql4, null);
			    }
			    while (set3.next()) {
					String sql5="update bus_lineandstation_info set godown="+set3.getInt(1)+"-1";
					count2++;
					j=helper.executeUpdateByPrepareStatement(sql5, null);
			    }
			    if (i==count1&&j==count2) {
			    	String sql6="delete from bus_station_info where stationId="+stationId;
			    	String sql7="delete from ";
					return true;
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}
	
	public List<String> searchStation(String stationText)
	{
		List<String> list=new ArrayList<String>(0);
		String  sql="select a.stationName from bus_station_info a where a.stationName like '%"+stationText+"%'";
		ResultSet rs=helper.executeQueryByPrepareStatement(sql, null);
		try {
			while(rs.next())
			{
				list.add(rs.getString(1));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	

}
